﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using Data.Common;
using Data.Enum;
using Data.Model;
using Data.ViewModel;

namespace DataAccess.BikeManage
{
    /// <summary>
    /// 车辆管理类主要是和车辆相关的操作
    /// </summary>
    public class Bike : DbBase, IBike
    {
        /// <summary>
        /// 通过车辆id获取车辆详细信息
        /// </summary>
        /// <param name="id">车辆id</param>
        /// <returns></returns>
        public ResultInfo<bike> GetBike(int id)
        {
            var result = new ResultInfo<bike> { ResultNumber = ResultEnum.Error };
            try
            {
                var b = new bike();
                if (id > 0)
                {
                    var sql = string.Format("select * from bike where bikeid={0}", id);
                    var ds = ExcuteSqlDataSet(sql);
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        result.ResultNumber = ResultEnum.Success;
                        b = new bike(ds.Tables[0].Rows[0]);

                    }
                    else
                    {
                        result.ResultNumber = ResultEnum.Error;
                        result.Describe = "不存在该车辆";
                    }
                    result.Obj = b;
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "查询车辆失败";
                }

            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }

            return result;
        }

        /// <summary>
        /// 分页查询车辆列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="bikecode">车辆标志</param>
        /// <param name="lockaddress">停车点名称</param>
        /// <param name="controlbox">控制箱名称</param>
        /// <param name="status">车辆状态</param>
        /// <returns></returns>
        public ResultInfo<PagingDataSet<BikeView>> GetBikes(int pageIndex, int pageSize, string bikecode, string lockaddress, string controlbox, int status)
        {
            var result = new ResultInfo<PagingDataSet<BikeView>>();
            try
            {
                using (SqlCommand myCommand = GetProCommand())
                {
                    myCommand.CommandText = "Common_GetPagingRecords";
                    var fromClause = new StringBuilder();
                    fromClause.Append("bike with (nolock) ");
                    var whereClause = new StringBuilder();
                    if (!string.IsNullOrEmpty(bikecode))
                    {
                        whereClause.Append(string.Format(" and bikecode='{0}' ", bikecode));
                    }
                    if (!string.IsNullOrEmpty(lockaddress))
                    {
                        whereClause.Append(string.Format(" and lockpileid in (select id from lockaddress where name like '%{0}%') ", lockaddress));
                    }
                    if (!string.IsNullOrEmpty(controlbox))
                    {
                        whereClause.Append(string.Format(" and controlboxid in (select id from jsh_controlbox where boxid like '%{0}%') ", controlbox));
                    }
                    if (status >= 0)
                    {
                        whereClause.Append(string.Format(" and status={0} ", status));
                    }
                    if (whereClause.Length > 3)
                    {
                        whereClause.Remove(0, 5);
                    }

                    myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex - 1;
                    myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                    myCommand.Parameters.Add("@FromClause", SqlDbType.VarChar, 1024).Value = fromClause.ToString();
                    myCommand.Parameters.Add("@SelectFields", SqlDbType.VarChar, 512).Value = "*";
                    myCommand.Parameters.Add("@WhereClause", SqlDbType.NVarChar, 4000).Value = whereClause.ToString();
                    myCommand.Parameters.Add("@SortField", SqlDbType.VarChar, 256).Value = "bikeid";
                    myCommand.Parameters.Add("@SortFieldIsDesc", SqlDbType.Bit).Value = true;
                    myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = true;
                    myCommand.Parameters.Add("@MaxRecords", SqlDbType.Int).Value = int.MaxValue;
                    myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                    myCommand.Parameters.Add("@ReverseOrder", SqlDbType.Bit).Direction = ParameterDirection.Output;

                    var dataSet = new DataSet();
                    var da = new SqlDataAdapter(myCommand);
                    da.Fill(dataSet);

                    var list = dataSet.Tables[0].Rows.Cast<DataRow>().Select(dataRow => new bike(dataRow)).Where(temp => temp.TranByConstructFlag).ToList();

                    int totalRecords = Convert.ToInt32(myCommand.Parameters["@TotalRecords"].Value);
                    bool reverseOrder = Convert.ToBoolean(myCommand.Parameters["@ReverseOrder"].Value);

                    if (reverseOrder)
                        list.Reverse();

                    var listView = PopulateBike(list);

                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = new PagingDataSet<BikeView> { PageIndex = pageIndex, PageSize = pageSize, Records = listView, TotalRecords = totalRecords };
                }
            }
            catch (SqlException)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = "数据库错误，请联系管理员！";
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 添加车辆信息
        /// </summary>
        /// <param name="bike">车辆详情</param>
        /// <returns></returns>
        public int AddBike(bike bike)
        {
            var result = 0;
            if (bike == null) return result;
            var sql =
                string.Format(
                    "insert into bike([bikecode],[password],[price],[bikenum],[status],[type],[memo],[lockpileid],[controlboxid]) values('{0}','{1}',{2},'{3}',{4},{5},'{6}',{7},{8}) select @@identity id", bike.bikecode, bike.password, bike.price, bike.bikenum, bike.status, bike.type, bike.memo, bike.lockpileid, bike.controlboxid);

            var dataSet = ExcuteSqlDataSet(sql);
            if (dataSet != null && dataSet.Tables[0].Rows.Count == 1)
            {
                result = Convert.ToInt32(dataSet.Tables[0].Rows[0]["id"]);
            }
            return result;
        }

        /// <summary>
        /// 通过id删除车辆信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ResultInfo<bool> DeleteBike(int id)
        {
            var result = new ResultInfo<bool>();
            try
            {
                string sql = string.Format("delete from bike where bikeid={0}", id);
                int temp = ExcuteSql(sql);
                if (temp > 0)
                {
                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = true;
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Obj = false;
                }
            }
            catch (Exception)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = "删除失败";
            }
            return result;
        }

        /// <summary>
        /// 创建更新车辆
        /// </summary>
        /// <param name="bike">车辆信息，如果是有车辆的id，那就是更新这个车辆信息，如果没有车辆的id就是添加一条车辆信息</param>
        /// <returns></returns>
        public ResultInfo<int> CreateOrUpdateBike(bike bike)
        {
            var result = new ResultInfo<int>();
            try
            {
                string sql = string.Empty;
                if (bike != null)
                {
                    if (bike.bikeid > 0)
                    {
                        sql = string.Format("UPDATE bike SET [bikecode]='{0}',[password]='{1}',[price]={2},[bikenum]='{3}',[status]={4},[type]={5},[memo]='{6}',[lockpileid]={7},[controlboxid]={8} where [bikeid]={9} select {9} id", bike.bikecode, bike.password, bike.price, bike.bikenum, bike.status, bike.type, bike.memo, bike.lockpileid, bike.controlboxid, bike.bikeid);

                    }
                    else
                    {
                        string check = string.Format("select bikeid from bike where bikecode='{0}'", bike.bikecode);
                        DataSet dt = ExcuteSqlDataSet(check);
                        if (dt != null && dt.Tables[0].Rows.Count > 0)
                        {
                            result.ResultNumber = ResultEnum.Error;
                            result.Describe = "已经存在相同的车辆编号，请重新输入";
                        }
                        else
                        {
                            sql = string.Format("insert into bike([bikecode],[password],[price],[bikenum],[status],[type],[memo],[lockpileid],[controlboxid]) values('{0}','{1}',{2},'{3}',{4},{5},'{6}',{7},{8}) select @@identity id", bike.bikecode, bike.password, bike.price, bike.bikenum, bike.status, bike.type, bike.memo, bike.lockpileid, bike.controlboxid);
                        }
                    }
                    if (!string.IsNullOrEmpty(sql))
                    {
                        DataSet dataSet = ExcuteSqlDataSet(sql);
                        if (dataSet != null && dataSet.Tables[0].Rows.Count == 1)
                        {
                            int temp = Convert.ToInt32(dataSet.Tables[0].Rows[0]["id"]);
                            if (temp > 0)
                            {
                                result.ResultNumber = ResultEnum.Success;
                                result.Obj = temp;
                            }
                            else
                            {
                                result.ResultNumber = ResultEnum.Error;
                                result.Describe = "更新数据失败";
                            }
                        }
                    }
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "请输入有效数据";
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        private List<BikeView> PopulateBike(IEnumerable<bike> bikes)
        {
            int i = 0;
            var bikeViews = new List<BikeView>();
            foreach (var b in bikes)
            {
                i++;
                bikeViews.Add(new BikeView
                {
                    Serial = i,
                    BikeCode = b.bikecode,
                    Id = b.bikeid,
                    BikeId = b.bikenum,
                    BikeType = b.type.ToString(CultureInfo.InvariantCulture),
                    ControlboxName = b.controlboxid.ToString(CultureInfo.InvariantCulture),
                    LockpileName = b.lockpileid.ToString(CultureInfo.InvariantCulture),
                    Memo = b.memo,
                    Price = b.price,
                    Status = b.status
                });
            }
            return bikeViews;
        }
    }
}
